﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Android.App;
using Android.Content;
using Android.OS;
using Android.Runtime;
using Android.Views;
using Android.Widget;
using Android.Database.Sqlite;
using Android.Database;
using Android.Util;
using StoveTop.Models;

namespace StoveTop
{
	class StoveTopDBHelper : SQLiteOpenHelper
	{
		public static readonly int DatabaseVersion = 5;
		new public static readonly string DatabaseName = "StoveTopDB";
		// Useful constants
		public static readonly string TABLE_RECIPES = "recipes";
		public static readonly string[] COLUMNS_RECIPES = {
			"id",
			"name",
			"preptime",
			"cooktime",
			"rating",
			"difficulty"
		};
		public static readonly string TABLE_STEPS = "steps";
		public static readonly string[] COLUMNS_STEPS = {
			"id",
			"stepnum",
			"instructions",
			"timer",
		};
		public static readonly string TABLE_INGREDIENTS = "ingredients";
		public static readonly string[] COLUMNS_INGREDIENTS = { "id", "name", "quant_owned", "units", "quant_in_list"};
		public static readonly string TABLE_EQUIPMENT = "equipment";
		public static readonly string[] COLUMNS_EQUIPMENT = { "id", "name", "is_owned" };
		public static readonly string TABLE_VOCAB = "vocab";
		public static readonly string[] COLUMNS_VOCAB = { "id", "name", "description" };
		public static readonly string TABLE_TAGS = "tags";
		public static readonly string[] COLUMNS_TAGS = { "id", "name" };
		public static readonly string TABLE_STEPS_VOCAB = "stepsvocab";
		public static readonly string[] COLUMNS_STEPS_VOCAB = { "id", "step_id", "vocab_id" };
		public static readonly string TABLE_RECIPES_INGREDIENTS = "recipesingredients";
		public static readonly string[] COLUMNS_RECIPES_INGREDIENTS = { "id", "recipe_id", "ingredient_id", "quant_required", "units", "prep_notes" };
		public static readonly string TABLE_RECIPES_STEPS = "recipessteps";
		public static readonly string[] COLUMNS_RECIPES_STEPS = { "id", "recipe_id", "step_id" };
		public static readonly string TABLE_RECIPES_TAGS = "recipestags";
		public static readonly string[] COLUMNS_RECIPES_TAGS = { "id", "recipe_id", "tag_id" };
		public static readonly string TABLE_STEPS_EQUIPMENT = "stepsequipment";
		public static readonly string[] COLUMNS_STEPS_EQUIPMENT = { "id", "step_id", "equipment_id" };
		public static readonly string TABLE_SHOPPINGLIST = "shoppinglist";
		public static readonly string[] COLUMNS_SHOPPINGLIST = { "id", "name", "quantity" };

		public static readonly string create_table_tags = "CREATE TABLE IF NOT EXISTS tags(id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE, " +
			"name TEXT NOT NULL UNIQUE);";
		public static readonly string create_table_vocab = "CREATE TABLE IF NOT EXISTS vocab(id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE," +
			"name TEXT NOT NULL UNIQUE, description TEXT);";
		public static readonly string create_table_equipment = "CREATE TABLE IF NOT EXISTS equipment(id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE," +
			"name TEXT NOT NULL UNIQUE, is_owned INTEGER);";
		public static readonly string create_table_ingredients = "CREATE TABLE IF NOT EXISTS ingredients(id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE," +
			"name TEXT NOT NULL UNIQUE, quant_owned REAL, units TEXT, quant_in_list REAL);";
		public static readonly string create_table_steps = "CREATE TABLE IF NOT EXISTS steps(id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE," +
			"stepnum INTEGER, instructions TEXT, timer INTEGER);";
		public static readonly string create_table_recipes = "CREATE TABLE IF NOT EXISTS recipes(id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE," +
			"name TEXT NOT NULL UNIQUE, preptime INTEGER, cooktime INTEGER, rating REAL, difficulty REAL);";
		public static readonly string create_table_steps_vocab = "CREATE TABLE IF NOT EXISTS stepsvocab(id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE," +
			" step_id INTEGER, vocab_id INTEGER, FOREIGN KEY (step_id) REFERENCES steps(id), FOREIGN KEY (vocab_id) REFERENCES vocab(id));";
		public static readonly string create_table_recipes_tags = "CREATE TABLE IF NOT EXISTS recipestags(id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE, " +
			"recipe_id INTEGER, tag_id INTEGER, FOREIGN KEY (recipe_id) REFERENCES recipes(id), FOREIGN KEY (tag_id) REFERENCES tags(id));";
		public static readonly string create_table_recipes_ingredients = "CREATE TABLE IF NOT EXISTS recipesingredients(id INTEGER PRIMARY KEY AUTOINCREMENT" +
			" UNIQUE , recipe_id INTEGER, ingredient_id INTEGER, quant_required REAL, units TEXT, prep_notes TEXT, FOREIGN KEY (recipe_id) REFERENCES recipes(id), " +
			"FOREIGN KEY (ingredient_id) REFERENCES ingredients(id));";
		public static readonly string create_table_recipes_steps = "CREATE TABLE IF NOT EXISTS recipessteps(id INTEGER PRIMARY KEY AUTOINCREMENT," +
			" recipe_id INTEGER, step_id INTEGER, FOREIGN KEY (recipe_id) REFERENCES recipes(id), FOREIGN KEY (step_id) REFERENCES steps(id));";
		public static readonly string create_table_steps_equipment = "CREATE TABLE IF NOT EXISTS stepsequipment(id INTEGER PRIMARY KEY AUTOINCREMENT," +
			" step_id INTEGER, equipment_id INTEGER, FOREIGN KEY (step_id) REFERENCES steps(id), FOREIGN KEY (equipment_id) REFERENCES equipment(id));";

		public StoveTopDBHelper (Context context) : base(context, DatabaseName, null, DatabaseVersion) {
		}
		public override void OnCreate (SQLiteDatabase db) {

			Log.Debug ("DEBUG", "StoveTopDBHelper.OnCreate() called!");
			db.ExecSQL (create_table_tags);
			Log.Info("DBHelper", "Created categories table");
			db.ExecSQL (create_table_vocab);
			Log.Info("DBHelper", "Created techniques table");
			db.ExecSQL (create_table_equipment);
			Log.Info ("DBHelper", "Created equipment table");
			db.ExecSQL (create_table_ingredients);
			Log.Info ("DBHelper", "Created ingredients table");
			db.ExecSQL (create_table_steps);
			Log.Info ("DBHelper", "Created steps table");
			db.ExecSQL (create_table_recipes);
			Log.Info ("DBHelper", "Created recipes table");
			db.ExecSQL (create_table_steps_vocab);
			db.ExecSQL (create_table_recipes_tags);
			db.ExecSQL (create_table_recipes_ingredients);
			db.ExecSQL (create_table_recipes_steps);
			db.ExecSQL (create_table_steps_equipment);

			// Insert some data
			db.ExecSQL ("INSERT INTO tags (name) VALUES ('lunch')");
			db.ExecSQL ("INSERT INTO tags (name) VALUES ('dinner')");
			db.ExecSQL ("INSERT INTO tags (name) VALUES ('breakfast')");
			Log.Info ("DBHelper", "Seeded tags");

			//db.ExecSQL ("INSERT INTO categories (name) VALUES (appetizer)");

			db.ExecSQL ("INSERT INTO vocab (name, description) VALUES ('bake', 'To cook in an oven by using heat')");
			db.ExecSQL ("INSERT INTO vocab (name, description) VALUES ('boil', 'To heat water until little bubbles form')");
			db.ExecSQL ("INSERT INTO vocab (name, description) VALUES ('carve', 'To cut meat into slices')");
			// http://www.vocabulary.cl/Lists/Cooking-Instructions.htm
			// It should be pretty easy to parse the above page and use it to seed the techniques table without having to pack by hand
			// -- Using AddTechnique() method

			db.ExecSQL ("INSERT INTO equipment (name, is_owned) VALUES ('fork', '1')");
			db.ExecSQL ("INSERT INTO equipment (name, is_owned) VALUES ('spoon', '1')");
			db.ExecSQL ("INSERT INTO equipment (name, is_owned) VALUES ('spork', '0')");
			Log.Debug ("DEBUG", "Seeded sample equipment data");

		}

		public override void OnUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
		{
			db.ExecSQL ("DROP TABLE IF EXISTS recipes");
			db.ExecSQL ("DROP TABLE IF EXISTS steps");
			db.ExecSQL ("DROP TABLE IF EXISTS ingredients");
			db.ExecSQL ("DROP TABLE IF EXISTS equipment");
			db.ExecSQL ("DROP TABLE IF EXISTS techniques");
			db.ExecSQL ("DROP TABLE IF EXISTS categories");
			db.ExecSQL ("DROP TABLE IF EXISTS vocab");
			db.ExecSQL ("DROP TABLE IF EXISTS tags");
			db.ExecSQL ("DROP TABLE IF EXISTS stepsvocab");
			db.ExecSQL ("DROP TABLE IF EXISTS stepstags");
			db.ExecSQL ("DROP TABLE IF EXISTS stepsingredients");
			db.ExecSQL ("DROP TABLE IF EXISTS recipessteps");
			db.ExecSQL ("DROP TABLE IF EXISTS stepsequiment");
			db.ExecSQL ("DROP TABLE IF EXISTS recipesingredients");
			this.OnCreate (db);
		}

		// Recipe CRUD Methods
		public int AddRecipeWithTags(StoveTop.Models.Recipe recipe, int[] tagIds)
		{
			SQLiteDatabase db = WritableDatabase;
			ContentValues values = new ContentValues ();
			values.Put ("name", recipe.GetName ());
			values.Put ("preptime", recipe.GetPrepTime ());
			values.Put ("cooktime", recipe.GetCookTime ());
			values.Put ("rating", (double) recipe.GetRating ());
			values.Put ("difficulty", (double) recipe.GetDifficulty ());
			int recipeId = (int) db.Insert (TABLE_RECIPES, null, values);
			foreach (int tagId in tagIds) {
				AddRecipeTag (recipeId, tagId);
			}
			return recipeId;
		}
		public int AddRecipe(StoveTop.Models.Recipe recipe)
		{
			SQLiteDatabase db = WritableDatabase;
			ContentValues values = new ContentValues ();
			values.Put ("name", recipe.GetName ());
			values.Put ("preptime", recipe.GetPrepTime ());
			values.Put ("cooktime", recipe.GetCookTime ());
			values.Put ("rating", (double) recipe.GetRating ());
			values.Put ("difficulty", (double) recipe.GetDifficulty ());
			int recipeId = (int) db.Insert (TABLE_RECIPES, null, values);
			return recipeId;
		}
		public StoveTop.Models.Recipe GetRecipe(int recipeId)
		{
			SQLiteDatabase db = ReadableDatabase;
			string selectQuery = "SELECT * FROM " + TABLE_RECIPES + " WHERE id = " + recipeId.ToString ();
			ICursor c = db.RawQuery (selectQuery, null);
			StoveTop.Models.Recipe recipe = null;
			if (c != null) {
				c.MoveToFirst ();
				recipe = new StoveTop.Models.Recipe (c.GetInt (0), c.GetString (1), c.GetInt (2), c.GetInt (3), (decimal)c.GetDouble (4), (decimal)c.GetDouble (5));
			}
			return recipe;
		}
		public List <StoveTop.Models.Recipe> GetAllRecipes() 
		{
			List <StoveTop.Models.Recipe> recipes = new List <StoveTop.Models.Recipe> ();
			string selectQuery = "SELECT * FROM " + TABLE_RECIPES;
			SQLiteDatabase db = ReadableDatabase;
			ICursor c = db.RawQuery (selectQuery, null);
			if (c.MoveToFirst ()) {
				do {
					StoveTop.Models.Recipe recipe = new StoveTop.Models.Recipe (c.GetInt (0), c.GetString (1), c.GetInt (2), c.GetInt (3), (decimal)c.GetDouble (4), (decimal)c.GetDouble (5));
					recipes.Add (recipe);
				} while (c.MoveToNext ());
			}
			return recipes;
		}
		public List <StoveTop.Models.Recipe> GetAllRecipesWithTag(string tagName) {
			List <StoveTop.Models.Recipe> recipes = new List <StoveTop.Models.Recipe> ();
			string selectQuery = "SELECT * FROM " + TABLE_RECIPES + " r, " + TABLE_TAGS + " t, " + TABLE_RECIPES_TAGS + " rt WHERE" +
				" t.name = \"" + tagName + "\" AND t.id = rt.tag_id AND r.id = rt.recipe_id";
			SQLiteDatabase db = ReadableDatabase;
			ICursor c = db.RawQuery (selectQuery, null);
			if (c.MoveToFirst ()) {
				do {
					StoveTop.Models.Recipe recipe = new StoveTop.Models.Recipe (c.GetInt (0), c.GetString (1), c.GetInt (2), c.GetInt (3), (decimal)c.GetDouble (4), (decimal)c.GetDouble (5));
					recipes.Add (recipe);
				} while (c.MoveToNext ());
			}
			return recipes;
		}
		public int UpdateRecipe(StoveTop.Models.Recipe recipe) {
			SQLiteDatabase db = WritableDatabase;
			ContentValues values = new ContentValues ();
			values.Put ("name", recipe.GetName ());
			values.Put ("preptime", recipe.GetPrepTime ());
			values.Put ("cooktime", recipe.GetCookTime ());
			values.Put ("rating", (double) recipe.GetRating ());
			values.Put ("difficulty", (double) recipe.GetDifficulty ());
			return db.Update (TABLE_RECIPES, values, "id = ?", new [] { recipe.GetId ().ToString () });
		}
		public void DeleteRecipe(int recipeId) {
			SQLiteDatabase db = WritableDatabase;
			db.Delete (TABLE_RECIPES, "id = ?", new [] { recipeId.ToString () });
		}

		// Steps CRUD Methods
		public int AddStep(Step step, int recipeId)
		{
			SQLiteDatabase db = WritableDatabase;
			ContentValues values = new ContentValues ();
			values.Put ("stepnum", step.GetStepNum ());
			values.Put ("instructions", step.GetInstructions ());
			values.Put ("timer", step.GetTimer ());
			int stepId = (int) db.Insert (TABLE_STEPS, null, values);
			AddRecipeStep (recipeId, stepId);
			return stepId;
		}
		public Step GetStep(int stepId)
		{
			SQLiteDatabase db = ReadableDatabase;
			string queryString = "SELECT * FROM " + TABLE_STEPS + " WHERE id = " + stepId.ToString ();
			ICursor c = db.RawQuery (queryString, null);
			if (c != null)
				c.MoveToFirst ();
			Step step = new Step (c.GetInt (0), c.GetInt (1), c.GetString (2), c.GetInt (3));
			return step;
		}
		public List <Step> GetAllStepsForRecipe(int recipeId)
		{
			List <Step> steps = new List <Step> ();
			SQLiteDatabase db = ReadableDatabase;
			string queryString = "SELECT * FROM " + TABLE_STEPS + " s, " + TABLE_RECIPES + " r, " + TABLE_RECIPES_STEPS + " rs WHERE " +
				"s.id = rs.step_id AND r.id = rs.recipe_id AND r.id = " + recipeId.ToString ();
			ICursor c = db.RawQuery (queryString, null);
			if (c.MoveToFirst ()) {
				do {
					Step step = new Step (c.GetInt (0), c.GetInt (1), c.GetString (2), c.GetInt (3));
					steps.Add (step);
				} while (c.MoveToNext ());
			}
			return steps;
		}

		//RecipesSteps CRUD Methods
		public int AddRecipeStep(int recipeId, int stepId) 
		{
			SQLiteDatabase db = WritableDatabase;
			ContentValues values = new ContentValues ();
			values.Put ("recipe_id", recipeId);
			values.Put ("step_id", stepId);
			int id = (int) db.Insert (TABLE_RECIPES_STEPS, null, values);
			return id;
		}

		//RecipesTags CRUD Methods
		public int AddRecipeTag(int recipeId, int tagId) {
			SQLiteDatabase db = WritableDatabase;
			ContentValues values = new ContentValues ();
			values.Put("recipe_id", recipeId);
			values.Put ("tag_id", tagId);
			int id = (int) db.Insert(TABLE_RECIPES_TAGS, null, values);
			return id;
		}

		// Tag CRUD Methods
		public int AddTag(Tag tag)
		{
			SQLiteDatabase db = WritableDatabase;
			ContentValues values = new ContentValues ();
			values.Put ("name", tag.GetName ());
			int id = (int) db.Insert (TABLE_TAGS, null, values);
			db.Close ();
			return id;
		}
		public Tag GetTag(int id)
		{
			Log.Info ("DBHelper", "getTag() called");
			SQLiteDatabase db = ReadableDatabase;
			ICursor cursor = db.Query (TABLE_TAGS, COLUMNS_TAGS, "id = ?", new [] {id.ToString()}, null, null, null, null);
			if (cursor != null) {
				cursor.MoveToFirst ();
			}
			Tag tag = new Tag (cursor.GetInt (0), cursor.GetString (1));
			return tag;
		}
		public List <Tag> GetAllTags() 
		{
			List<Tag> tags = new List<Tag> ();
			String query = "SELECT * FROM " + TABLE_TAGS;
			SQLiteDatabase db = WritableDatabase;
			ICursor cursor = db.RawQuery (query, null);
			Tag tag = null;
			if (cursor.MoveToFirst ()) {
				do {
					tag = new Tag ();
					tag.SetId (cursor.GetInt (0));
					tag.SetName (cursor.GetString (1));
					tags.Add (tag);
				} while (cursor.MoveToNext ());
			}
			Log.Debug ("getAllTags()", tags.ToString ());

			return tags;
		}
		public List <Tag> GetAllTagsForRecipe(int recipeId)
		{
			List<Tag> tags = new List<Tag> ();
			String query = "SELECT * FROM " + TABLE_TAGS + " t, " + TABLE_RECIPES + " r, " + TABLE_RECIPES_TAGS + " rt WHERE rt.recipe_id = r.id AND " +
			               "rt.tag_id = t.id AND r.id = " + recipeId.ToString ();
			SQLiteDatabase db = ReadableDatabase;
			ICursor c = db.RawQuery (query, null);
			Tag tag = null;
			if (c.MoveToFirst ()) {
				do {
					tag = new Tag ();
					tag.SetId (c.GetInt (0));
					tag.SetName (c.GetString (1));
					tags.Add (tag);
				} while (c.MoveToNext ());
			}
			Log.Debug ("GetAllTagsForRecipe()", tags.ToString ());
			return tags;
		}
		public void DeleteTag(int id)
		{
			SQLiteDatabase db = WritableDatabase;
			db.Delete (TABLE_TAGS, "id = ?", new [] {id.ToString ()});
			db.Close ();
		}

		// Techniques CRUD Methods
		public int AddVocab(Vocab vocab)
		{
			SQLiteDatabase db = WritableDatabase;
			ContentValues values = new ContentValues ();
			values.Put ("name", vocab.GetName ());
			values.Put ("description", vocab.GetDescription ());
			int id = (int) db.Insert (TABLE_VOCAB, null, values);
			db.Close ();
			return id;
		}
		public Vocab GetVocab(int id)
		{
			Log.Info ("DBHelper", "getTechnique() called");
			SQLiteDatabase db = ReadableDatabase;
			ICursor cursor = db.Query (TABLE_VOCAB, COLUMNS_VOCAB, "id = ?", new [] {id.ToString()}, null, null, null, null);
			if (cursor != null) {
				cursor.MoveToFirst ();
			}
			Vocab vocab = new Vocab (cursor.GetInt (0), cursor.GetString (1), cursor.GetString(2));
			return vocab;
		}
		public List <Vocab> GetAllVocab() 
		{
			List<Vocab> vocabList = new List<Vocab> ();
			String query = "SELECT * FROM " + TABLE_VOCAB;
			SQLiteDatabase db = ReadableDatabase;
			ICursor cursor = db.RawQuery (query, null);
			Vocab vocab = null;
			if (cursor.MoveToFirst ()) {
				do {
					vocab = new Vocab ();
					vocab.SetId (cursor.GetInt (0));
					vocab.SetName (cursor.GetString (1));
					vocab.SetDescription (cursor.GetString(2));
					vocabList.Add (vocab);
				} while (cursor.MoveToNext ());
			}
			Log.Debug ("getAllTechniques()", vocabList.ToString ());

			return vocabList;
		}
		public void DeleteVocab(int id)
		{
			SQLiteDatabase db = WritableDatabase;
			db.Delete (TABLE_VOCAB, "id = ?", new [] {id.ToString ()});
			db.Close ();
		}
			
		// Equipment CRUD Methods
		public int AddEquipment(Equipment equipment)
		{
			SQLiteDatabase db = WritableDatabase;
			ContentValues values = new ContentValues ();
			values.Put ("name", equipment.GetName ());
			values.Put ("is_owned", Convert.ToInt32 (equipment.IsOwned ()));
			int id = (int) db.Insert (TABLE_EQUIPMENT, null, values);
			db.Close ();
			return id;
		}
		public Equipment GetEquipment(int id)
		{
			Log.Info ("DBHelper", "getEquipment() called");
			SQLiteDatabase db = ReadableDatabase;
			ICursor cursor = db.Query (TABLE_EQUIPMENT, COLUMNS_EQUIPMENT, "id = ?", new [] {id.ToString()}, null, null, null, null);
			if (cursor != null) {
				cursor.MoveToFirst ();
			}
			Equipment equip = new Equipment (cursor.GetInt (0), cursor.GetString (1), (cursor.GetInt(2)) > 0);
			return equip;
		}
		public List <Equipment> GetAllEquipment() 
		{
			List<Equipment> equips = new List<Equipment> ();
			String query = "SELECT * FROM " + TABLE_EQUIPMENT;
			SQLiteDatabase db = ReadableDatabase;
			ICursor cursor = db.RawQuery (query, null);
			Equipment equip = null;
			if (cursor.MoveToFirst ()) {
				do {
					equip = new Equipment ();
					equip.SetId (cursor.GetInt (0));
					equip.SetName (cursor.GetString (1));
					equip.SetOwned (cursor.GetInt(2) == 1);
					equips.Add (equip);
				} while (cursor.MoveToNext ());
			}
			Log.Debug ("getAllEquipment()", equips.ToString ());

			return equips;
		}
		public void DeleteEquipment(int id)
		{
			SQLiteDatabase db = WritableDatabase;
			db.Delete (TABLE_EQUIPMENT, "id = ?", new [] {id.ToString ()});
			db.Close ();
		}

		// Ingredients CRUD Methods
		public int AddIngredient(Ingredient ingredient) {
			Console.WriteLine ("Adding ingredient " + ingredient.GetName ());
			SQLiteDatabase db = WritableDatabase;
			ContentValues values = new ContentValues ();
			values.Put ("name", ingredient.GetName ());
			values.Put ("quant_owned", (double) ingredient.GetQuantityOnHand ());
			values.Put ("quant_in_list", (double) ingredient.GetQuantityInList ());
			values.Put ("units", ingredient.GetUnits ());
			int id = (int) db.Insert (TABLE_INGREDIENTS, null, values);
			db.Close ();
			return id;
		}
		// Add new ingredient "ingredientName" to DB. Return ingredientId; if exists already, return existing id
		public int AddIngredient(string ingredientName) {
			SQLiteDatabase db = WritableDatabase;
			ContentValues values = new ContentValues ();
			values.Put ("name", ingredientName);
			values.Put ("quant_owned", 0);
			values.Put ("quant_in_list", 0);
			int id = (int)db.Insert (TABLE_INGREDIENTS, null, values);
			db.Close ();
			return id;
		}
		public Ingredient GetIngredient(int id)
		{
			Log.Info ("DBHelper", "getIngredient() called");
			Log.Debug ("DBHelper", "GetIngredient(" + id.ToString () + ") called");
			SQLiteDatabase db = ReadableDatabase;
			ICursor cursor = db.Query (TABLE_INGREDIENTS, COLUMNS_INGREDIENTS, "id = ?", new [] {id.ToString()}, null, null, null, null);
			if (cursor != null) {
				cursor.MoveToFirst ();
			}
			Ingredient ingredient = new Ingredient (cursor.GetInt (0), cursor.GetString (1), cursor.GetInt(2), cursor.GetString(3), cursor.GetInt(4));
			return ingredient;
		}
		public Ingredient GetIngredient(string name)
		{
			SQLiteDatabase db = ReadableDatabase;
			Console.Write ("GetIngredient(" + name + ")");
			ICursor c = db.Query (TABLE_INGREDIENTS, COLUMNS_INGREDIENTS, "name = ?", new [] { name }, null, null, null, null);
			if (c != null) {
				c.MoveToFirst ();
			}
			Ingredient ingredient = new Ingredient (c.GetInt (0), c.GetString (1), c.GetInt(2), c.GetString(3), c.GetInt(4));
			return ingredient;
		}
		public List <Ingredient> GetAllIngredients() 
		{
			List<Ingredient> ingredients = new List<Ingredient> ();
			String query = "SELECT * FROM " + TABLE_INGREDIENTS;
			SQLiteDatabase db = ReadableDatabase;
			ICursor cursor = db.RawQuery (query, null);
			Ingredient ingred = null;
			if (cursor.MoveToFirst ()) {
				do {
					ingred = new Ingredient ();
					ingred.SetId (cursor.GetInt (0));
					ingred.SetName (cursor.GetString (1));
					ingredients.Add (ingred);
				} while (cursor.MoveToNext ());
			}
			Log.Debug ("getAllIngredients()", ingredients.ToString ());

			return ingredients;
		}
		public List <Ingredient> GetAllIngredientsInShoppingList()
		{
			List<Ingredient> ingredients = new List <Ingredient> ();
			String query = "SELECT * FROM " + TABLE_INGREDIENTS + " WHERE quant_in_list > 0";
			SQLiteDatabase db = ReadableDatabase;
			ICursor cursor = db.RawQuery (query, null);
			Ingredient ingred = null;
			if (cursor.MoveToFirst ()) {
				do {
					ingred = new Ingredient ();
					ingred.SetId (cursor.GetInt (0));
					ingred.SetName (cursor.GetString (1));
					ingred.SetQuantityInList(cursor.GetInt(4));
					ingred.SetUnits(cursor.GetString(3));
					ingredients.Add (ingred);
				} while (cursor.MoveToNext ());
			}
			return ingredients;
		}
		public List <Ingredient> GetAllIngredientsInInventory()
		{
			List<Ingredient> ingredients = new List <Ingredient> ();
			String query = "SELECT * FROM " + TABLE_INGREDIENTS + " WHERE quant_owned > 0";
			SQLiteDatabase db = ReadableDatabase;
			ICursor cursor = db.RawQuery (query, null);
			Ingredient ingred = null;
			if (cursor.MoveToFirst ()) {
				do {
					ingred = new Ingredient ();
					ingred.SetId (cursor.GetInt (0));
					ingred.SetName (cursor.GetString (1));
					ingred.SetQuantityOnHand ((decimal)cursor.GetDouble(2));
					ingred.SetUnits(cursor.GetString(3));
					ingredients.Add (ingred);
				} while (cursor.MoveToNext ());
			}
			return ingredients;
		}
		public int AddToInventory (int ingredientId, decimal quant) {
			SQLiteDatabase db = WritableDatabase;
			Ingredient ingred = GetIngredient (ingredientId);
			decimal quantOwned = ingred.GetQuantityOnHand ();
			ContentValues values = new ContentValues();
			values.Put ("quant_owned", (double) (quantOwned + quant));
			return db.Update (TABLE_INGREDIENTS, values, "id = ?", new [] { ingredientId.ToString() });
		}
		public int AddToShoppingList (int ingredientId, decimal quant) {
			SQLiteDatabase db = ReadableDatabase;
			Ingredient ingred = GetIngredient (ingredientId);
			decimal quantInList = ingred.GetQuantityInList ();
			ContentValues values = new ContentValues();
			values.Put ("quant_in_list", (double) (quantInList + quant));
			return db.Update (TABLE_INGREDIENTS, values, "id = ?", new [] { ingredientId.ToString() });
		}
		public int SubtractFromInventory (int ingredientId, decimal quant) {
			SQLiteDatabase db = WritableDatabase;
			Ingredient ingred = GetIngredient (ingredientId);
			decimal quantOnHand = ingred.GetQuantityOnHand();
			ContentValues values = new ContentValues();
			if (quantOnHand < quant) {
				values.Put ("quant_on_hand", 0);
			} else
				values.Put ("quant_on_hand",(double) (quantOnHand - quant));
			return db.Update(TABLE_INGREDIENTS, values, "id = ?", new [] { ingredientId.ToString() });
		}
		public int SubtractFromShoppingList (int ingredientId, decimal quant) {
			SQLiteDatabase db = WritableDatabase;
			Ingredient ingred = GetIngredient (ingredientId);
			decimal quantInList = ingred.GetQuantityInList();
			ContentValues values = new ContentValues();
			if (quantInList < quant) {
				values.Put ("quant_in_list", 0);
			} else
				values.Put ("quant_in_list", (double) (quantInList - quant));
			return db.Update(TABLE_INGREDIENTS, values, "id = ?", new [] { ingredientId.ToString() });
		}
		public void DeleteIngredient(int id)
		{
			SQLiteDatabase db = WritableDatabase;
			db.Delete (TABLE_INGREDIENTS, "id = ?", new [] {id.ToString ()});
			db.Close ();
		}
		public void ClearIngredientFromList (int ingredientId){
			SQLiteDatabase db = WritableDatabase;
			ContentValues values = new ContentValues ();
			values.Put ("quant_in_list", 0);
			db.Update (TABLE_INGREDIENTS, values, "id = ?", new [] { ingredientId.ToString () });
		}
		public void ClearIngredientFromInventory (int ingredientId){
			SQLiteDatabase db = WritableDatabase;
			ContentValues values = new ContentValues ();
			values.Put ("quant_owned", 0);
			db.Update (TABLE_INGREDIENTS, values, "id = ?", new [] { ingredientId.ToString () });
		}
		public int AddRecipeIngredient (int recipeId, string ingredName, decimal quantRequired, string units, string prepNotes) {
			SQLiteDatabase db = WritableDatabase;
			Ingredient ingred = GetIngredient (ingredName);
			Log.Debug ("AddRecipeIngredient()", "ingred=" + ingredName + ", id=" + ingred.GetId ().ToString ());
			ContentValues values = new ContentValues ();
			values.Put ("recipe_id", recipeId);
			values.Put ("ingredient_id", ingred.GetId());
			values.Put ("quant_required", (double) quantRequired);
			values.Put ("units", units);
			values.Put ("prep_notes", prepNotes);
			int id = (int) db.Insert (TABLE_RECIPES_INGREDIENTS, null, values);
			db.Close ();
			return id;
		}
		public List <StoveTop.Models.RecipeIngredient> GetAllIngredientsForRecipe(int recipeId)
		{
			List <StoveTop.Models.RecipeIngredient> ingreds = new List <StoveTop.Models.RecipeIngredient> ();
			string selectQuery = "SELECT * FROM " + TABLE_RECIPES_INGREDIENTS + " WHERE recipe_id = " + recipeId.ToString ();
			Console.WriteLine (selectQuery);
			SQLiteDatabase db = ReadableDatabase;
			ICursor c = db.RawQuery (selectQuery, null);
			if (c.MoveToFirst ()) {
				do {
					StoveTop.Models.RecipeIngredient ingred = new StoveTop.Models.RecipeIngredient (c.GetInt (0), c.GetInt (1), c.GetInt (2), (decimal) c.GetDouble (3), c.GetString(4), c.GetString(5));
					ingreds.Add (ingred);
				} while (c.MoveToNext ());
			}
			return ingreds;
		}

		public List <StoveTop.Models.Recipe> SearchForRecipes(string s) 
		{
			List <StoveTop.Models.Recipe> recipes = new List <StoveTop.Models.Recipe> ();
			string selectQuery = "SELECT * FROM " + TABLE_RECIPES + " WHERE UPPER(name) = UPPER(\"" + s + "\")";
			Console.WriteLine (selectQuery);
			SQLiteDatabase db = ReadableDatabase;
			ICursor c = db.RawQuery (selectQuery, null);
			if (c.MoveToFirst()) {
				do {
					StoveTop.Models.Recipe recipe = new StoveTop.Models.Recipe (c.GetInt (0), c.GetString (1), c.GetInt (2), c.GetInt (3), (decimal)c.GetDouble (4), (decimal)c.GetDouble (5));
					recipes.Add (recipe);
				} while (c.MoveToNext ());
			}
			return recipes;
		}

	}
}


